<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Specialization Analytics</title>
</head>
<body>
<center><h1>Specialization Analytics</h1></center>
<%	try {
		Class.forName("org.postgresql.Driver");
	} catch(Exception e) {
		out.println("org.postgresql.Driver Exception. ");
	}
	try {
		String serverName = "localhost:5432";
	    String mydatabase = "ThirdBase";
	    String url = "jdbc:postgresql://" + serverName + "/" + mydatabase; 

	    String username = "SweetAss";
	    String password = "1234";
	    Connection conn = DriverManager.getConnection(url, username, password);
		
	    //fetching total and names first, then selecting name and if total is null it becomes 0
	    String query = 
	    		"SELECT a.Name, COALESCE(b.total,0) AS \"Total Applicants\" " +
				"FROM specialization AS a " + 
					 "LEFT JOIN (	 SELECT specialization.Name, COUNT(*) as total " +
									"FROM applicant " +
									"JOIN specialization " + 
								    "ON applicant.specialization = specialization.id " +
							        "GROUP BY specialization.name) AS b " +
					 "ON a.name = b.name " +
				"ORDER BY a.name";
	    
		Statement stmt = conn.createStatement();
		ResultSet rset = stmt.executeQuery(query);

		out.println("<table width=\"600\" border=\"2\" cellpadding=\"2\" cellspacing=\"2\" align=\"center\">");
		//print out specializations
		while(rset.next()) {
			out.println("<tr>");
			out.println("<td>" + "<a href=\"http://localhost:8080/GradSchoolApp/applications.jsp?specName=" + rset.getString(1) + "\">" + rset.getString(2) + "</a>" + "</td>");
			out.println("<td>" + rset.getString(1) + "</td>");
			out.println("</tr>");
		}

		out.println("</table>");
		out.println("<br>");
		
		//close connections
		rset.close();
		stmt.close();
		conn.close();
	
	} catch(Exception e) {
		out.println("Connection exception.");
	}
%>
</body>
</html>